
<html><HEAD>
<LINK REL=STYLESHEET HREF="default.css" TYPE="text/css">
<TITLE>
Supported Oracle datatypes</TITLE>
</HEAD>
<BODY>

<!-- Header -->
<p class="ancestor" align="right"><A HREF="connpbp64.htm">Previous</A>&nbsp;&nbsp;<A HREF="connpbp66.htm" >Next</A>
<!-- End Header -->
<A NAME="BHCEGJDB"></A><h1>Supported Oracle datatypes</h1>
<A NAME="TI1342"></A><p>The Oracle database interfaces support the Oracle datatypes
listed in <br><A HREF="connpbp65.htm#BABBJEHC">Table 10-2</A> in DataWindow objects and embedded <ACRONYM title = "sequel" >SQL</ACRONYM>.</p>
<A NAME="BABBJEHC"></A><table cellspacing=0 cellpadding=6 border=1 frame="void" rules="all"><caption>Table 10-2: Supported datatypes for Oracle</caption>
<tr><td  rowspan="1"  ><A NAME="TI1343"></A>Binary_Float
(Oracle 10<i>g</i> and later only)</td>
<td  rowspan="1"  ><A NAME="TI1344"></A>LongRaw</td>
</tr>
<tr><td  rowspan="1"  ><A NAME="TI1345"></A>Binary_Double
(Oracle 10<i>g</i> and later only)</td>
<td  rowspan="1"  ><A NAME="TI1346"></A>NChar</td>
</tr>
<tr><td  rowspan="1"  ><A NAME="TI1347"></A>Bfile</td>
<td  rowspan="1"  ><A NAME="TI1348"></A>Number</td>
</tr>
<tr><td  rowspan="1"  ><A NAME="TI1349"></A>Blob</td>
<td  rowspan="1"  ><A NAME="TI1350"></A>NVarChar2 </td>
</tr>
<tr><td  rowspan="1"  ><A NAME="TI1351"></A>Char</td>
<td  rowspan="1"  ><A NAME="TI1352"></A>Raw</td>
</tr>
<tr><td  rowspan="1"  ><A NAME="TI1353"></A>Clob</td>
<td  rowspan="1"  ><A NAME="TI1354"></A>TimeStamp </td>
</tr>
<tr><td  rowspan="1"  ><A NAME="TI1355"></A>Date</td>
<td  rowspan="1"  ><A NAME="TI1356"></A>VarChar</td>
</tr>
<tr><td  rowspan="1"  ><A NAME="TI1357"></A>Float</td>
<td  rowspan="1"  ><A NAME="TI1358"></A>VarChar2</td>
</tr>
<tr><td  rowspan="1"  ><A NAME="TI1359"></A>Long</td>
<td  rowspan="1"  ><A NAME="TI1360"></A>XMLType (partial support, ORA driver
only)</td>
</tr>
</table>
<A NAME="TI1361"></A><p>The ORA driver adds support for the <b>XMLType</b> datatype
that was introduced with Oracle 9<i>i</i>. However,
you cannot use this datatype with embedded <ACRONYM title = "sequel" >SQL</ACRONYM> statements
or in a DataWindow object.</p>
<A NAME="TI1362"></A><h4>Accessing Unicode data</h4>
<A NAME="TI1363"></A><p>PowerBuilder can connect, save, and retrieve data in both ANSI/DBCS
and Unicode databases, but it does not convert data between Unicode
and ANSI/DBCS. When character data or command text is sent
to the database, PowerBuilder sends a Unicode string. The driver must
guarantee that the data is saved as Unicode data correctly. When PowerBuilder retrieves
character data, it assumes the data is Unicode.</p>
<A NAME="TI1364"></A><p>A Unicode database is a database whose character set is set
to a Unicode format, such as UTF-8, UTF-16, UCS-2, or UCS-4. All
data must be in Unicode format, and any data saved to the database
must be converted to Unicode data implicitly or explicitly.</p>
<A NAME="TI1365"></A><p>A database that uses ANSI (or DBCS) as its character set might
use special datatypes to store Unicode data. These datatypes are <b>NCHAR</b> and <b>NVARCHAR2</b>. Columns
with this datatype can store <i>only</i> Unicode data.
Any data saved into such a column must be converted to Unicode explicitly.
This conversion must be handled by the database server or client.</p>
<A NAME="TI1366"></A><p>A constant string is regarded as a char type by Oracle and
its character set is NLS_CHARACTERSET.  However, if the
datatype in the database is <b>NCHAR</b> and its character
set is NLS_NCHAR_CHARACTERSET, Oracle performs
a conversion from NLS_CHARACTERSET to NLS_NCHAR_CHARACTERSET.
This can cause loss of data. For example, if NLS_CHARACTERSET
is WE8ISO8859P1 and NLS_NCHAR_CHARACTERSET is
UTF8, when the Unicode data is mapped to WE8ISO8859P1, the Unicode
data is corrupted.</p>
<A NAME="TI1367"></A><p>If you want to access Unicode data using <b>NCHAR</b> and <b>NVARCHAR2</b> columns or
stored procedure parameters, use PowerBuilder variables to store the Unicode
data in a script using embedded SQL to avoid using a constant string, and
force PowerBuilder to bind the variables. </p>
<A NAME="TI1368"></A><p>By default, the Oracle database interfaces bind all string
data to internal variables as the Oracle <b>CHAR</b> datatype
to avoid downgrading performance. To ensure that <b>NCHAR</b> and <b>NVARCHAR2</b> columns
are handled as such on the server, set the NCharBind database parameter
to 1 to have the drivers bind string data as the Oracle <b>NCHAR</b> datatype.</p>
<A NAME="TI1369"></A><p>For example, suppose <b>table1</b> has a column <b>c1</b> with
the datatype <b>NVARCHAR2</b>. To insert Unicode data
into the table, set DisableBind to 0, set NCharBind to 1, and use
this syntax:<p><PRE> string var1<br>insert into table1 (c1) values(:var1);</PRE></p>
<A NAME="TI1370"></A><p>If an Oracle stored procedure has an <b>NCHAR</b> or <b>NVARCHAR2</b> input
parameter and the input data is a Unicode string, set the BindSPInput
database parameter to 1 to force the Oracle database to bind the
input data. The Oracle database interfaces are able to describe
the procedure to determine its parameters, therefore you do not
need to set the NCharBind database parameter.</p>
<A NAME="TI1371"></A><p>For a DataWindow object to access <b>NCHAR</b> and <b>NVARCHAR2</b> columns
and retrieve data correctly, set both DisableBind and StaticBind
to 0. Setting StaticBind to 0 ensures that PowerBuilder gets an accurate
datatype before retrieving.</p>
<A NAME="TI1372"></A><h4>TimeStamp datatype</h4>
<A NAME="TI1373"></A><p>The<b> TimeStamp</b> datatype in Oracle9<i>i</i> and
later is an extension of the <b>Date</b> datatype.
It stores the year, month, and day of the Date value plus hours, minutes,
and seconds:<p><PRE>Timestamp[<i>fractional_seconds_precision</i>]</PRE></p>
</p>
<A NAME="TI1374"></A><p>The<i> fractional_seconds_precision</i> value
is optional and provides the number of digits for indicating seconds.
The range of valid values for use with PowerBuilder is 0-6.</p>
<A NAME="TI1375"></A><h2>Datatype conversion</h2>
<A NAME="TI1376"></A><p>When you retrieve or update columns, in general PowerBuilder
converts data appropriately between the Oracle datatype and the
PowerScript datatype. Keep in mind, however, that similarly or identically
named Oracle and PowerScript datatypes do <i>not</i> necessarily
have the same definitions.</p>
<A NAME="TI1377"></A><p>For information about the definitions of PowerScript
datatypes, see the <i>PowerScript Reference</i>
.</p>
<A NAME="TI1378"></A><h4>Number datatype converted to decimal</h4>
<A NAME="TI1379"></A><p>When a DataWindow object is defined in PowerBuilder, the Oracle
datatype <b>number(size,d)</b> is mapped to a <b>decimal</b> datatype.
In PowerBuilder, the precision of a decimal is 18 digits. If a column's
datatype has a higher precision, for example <b>number(32,30)</b>,
inserting a number with a precision greater than 18 digits produces
an incorrect result when the number is retrieved in a DataWindow.
For example, 1.8E-17 displays as 0.000000000000000018, whereas 1.5E-25
displays as 0.</p>
<A NAME="TI1380"></A><p>You might be able to avoid this problem by using a different
datatype, such as <b>float</b>, for high precision
number columns in the Oracle DBMS. The <b>float</b> datatype
is mapped to the <b>number</b> datatype within the
DataWindow's source. </p>

